package de.fhluebeck.oop.gps.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import de.fhluebeck.oop.gps.common.Constants;
import de.fhluebeck.oop.gps.common.DependencyManager;
import de.fhluebeck.oop.gps.common.Singleton;
import de.fhluebeck.oop.gps.common.exception.NoDataFoundException;
import de.fhluebeck.oop.gps.dao.IDAO;
import de.fhluebeck.oop.gps.db.Database;
import de.fhluebeck.oop.gps.entity.Category;
import de.fhluebeck.oop.gps.entity.Poi;

/**
 * 
 * In dieser Klasse sind alle Funktionalitäten für die Datenbankoperationen der Entität
 * <br>Poi implementiert. 
 * 
 * Diese Klasse wird als Singleton implementiert.
 * 
 * 
 * @author Ogün Bilge, Leif Hitzschke
 * @version 1.00, 05.11.2009
 * 
 */
public class PoiDAO extends GenericDAO<Poi> implements Singleton{
	private final static Logger log = LoggerFactory.getLogger(PoiDAO.class);

	private Database database;

	private IDAO<Category> categoryDAO;
	
	private final static String TABLE_NAME="poi";

	// Holder Idiom Pattern
	private static class Holder {
		private static final PoiDAO instance = new PoiDAO();
	}

	public static PoiDAO getInstance() {
		return Holder.instance;
	}

	private PoiDAO() {
		super();
		categoryDAO = (IDAO<Category>) DependencyManager.locate(Constants.CATEGORY_DEP_NAME);
	}

	public void setDatabase(Database database) {
		this.database = database;
	}
	
	@Override
	public Poi create(Poi entity) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet results = null;
		try {
			StringBuilder sql = new StringBuilder();
			sql
					.append("insert into ").append(TABLE_NAME).append(" ( name, timestmp, category_id, lat, lng, description) values(?,?,?,?,?,?)");

			conn = database.getConnection();

			pstmt = conn.prepareStatement(sql.toString(),
					Statement.RETURN_GENERATED_KEYS);
			int index = 1;
			pstmt.setString(index++, entity.getName());
			pstmt.setTimestamp(index++, new Timestamp(entity.getTimestmp().getTime()));
			pstmt.setLong(index++, entity.getCategory().getId());
			pstmt.setDouble(index++, entity.getLat());
			pstmt.setDouble(index++, entity.getLng());
			pstmt.setString(index++, entity.getDescription());

			pstmt.execute();
			results = pstmt.getGeneratedKeys();
			if (results.next()) {
				long id = results.getLong(1);
				entity.setId(id);
				log.debug("got back id: {}", id);
			}
			return entity;
		} finally {
			close(results);
			close(pstmt);
			close(conn);
		}

	}

	@Override
	public void delete(Poi entity) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("delete from ").append(TABLE_NAME).append(" where id = ?");

			conn = database.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setLong(1, entity.getId());
			pstmt.execute();
		} finally {
			close(pstmt);
			close(conn);
		}
	}
	
	private Poi mapFromResult(ResultSet rs) throws SQLException{
		Poi gps = new Poi();
		gps.setName(rs.getString("name"));
		gps.setId(rs.getLong("id"));
		gps.setTimestmp(rs.getTimestamp("timestmp"));
		gps.setDescription(rs.getString("description"));
		gps.setLat(rs.getDouble("lat"));
		gps.setLng(rs.getDouble("lng"));
		gps.setCategory(categoryDAO.findById(rs.getLong("category_id")));

		return gps;
	}
	
	@Override
	public List<Poi> readAll() throws SQLException {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("select * from ").append(TABLE_NAME).append(" order by timestmp desc");

			conn = database.getConnection();

			pstmt = conn.prepareStatement(sql.toString());

			rs = pstmt.executeQuery();
			ArrayList<Poi> list = new ArrayList<Poi>();
			while (rs.next()) {
				list.add( mapFromResult(rs));
			}
			
			return list;
			
		} finally {
			close(rs);
			close(pstmt);
			close(conn);
		}
	}

	@Override
	public Poi findById(Long id) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("select * from ").append(TABLE_NAME).append(" where id = ?");

			conn = database.getConnection();

			pstmt = conn.prepareStatement(sql.toString());

			pstmt.setLong(1, id);

			rs = pstmt.executeQuery();
			if(rs.next()) {
				return mapFromResult(rs);
			}
			
			throw new NoDataFoundException("GPS with id " + id + " not in Database");
			
		} finally {
			close(rs);
			close(pstmt);
			close(conn);
		}
	}

	@Override
	public Poi update(Poi entity) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			StringBuilder sql = new StringBuilder();
			sql
					.append("update ").append(TABLE_NAME).append(" set timestmp = current_timestamp, name=?, category_id=?, lat=?, lng=?, description=? where id = ?");

			conn = database.getConnection();

			pstmt = conn.prepareStatement(sql.toString());
			int index = 1;
			pstmt.setString(index++, entity.getName());
			pstmt.setLong(index++, entity.getCategory().getId());
			pstmt.setDouble(index++, entity.getLat());
			pstmt.setDouble(index++, entity.getLng());
			pstmt.setString(index++, entity.getDescription());
			pstmt.setLong(index++, entity.getId());
			pstmt.executeUpdate();

			return findById(entity.getId());
		} finally {
			close(pstmt);
			close(conn);
		}
	}

	@Override
	public void initialize() throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("create table ");
			sql.append(TABLE_NAME);
			sql.append(" (");
			sql.append("id int primary key  GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)");
			sql.append(",name varchar(255)");
			sql.append(",lat double ");
			sql.append(",lng double ");
			sql.append(",description varchar(4000)");
			sql.append(",timestmp TIMESTAMP");
			sql.append(",category_id int");
			sql.append(",CONSTRAINT CATEGORY_FK FOREIGN KEY(category_id) REFERENCES category(id) on delete cascade )");

			conn = database.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.execute();
		} finally {
			close(pstmt);
			close(conn);
		}
	}

	@Override
	public boolean isValid() {
		boolean ret = false;
		StringBuilder sql = new StringBuilder();
		sql.append("select * from ").append(TABLE_NAME).append(" where 1=0");
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = database.getConnection();
			stmt = conn.prepareStatement(sql.toString());
			ret = stmt.execute();
		} catch (SQLException e) {
			log.debug("GPSTable does not exist");
		} finally {
			close(stmt);
			close(conn);
		}
		return ret;
	}

	@Override
	public void drop() throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("drop table ").append(TABLE_NAME);

			conn = database.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.execute();
		} finally {
			close(pstmt);
			close(conn);
		}
	}
}